package cn.calm.repaymentNotice.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import cn.calm.repaymentNotice.client.RepaymentNoticeException;
import cn.calm.repaymentNotice.entity.CreditCardRecord;
import cn.calm.repaymentNotice.entity.CreditPlanRecord;
import cn.calm.repaymentNotice.entity.RepaymentNotice;
import cn.calm.repaymentNotice.entity.RepaymentNoticeInfo;
import cn.calm.repaymentNotice.util.DateUtil;

public class DBService {
	private SQLiteHelper helper;
	private SQLiteDatabase db;
	private static DBService dao;

	private DBService(Context context) {
		helper = new SQLiteHelper(context);
	}

	public static DBService getInstance(Context context) {
		if (dao == null) {
			dao = new DBService(context);
		}
		return dao;
	}

	/**
	 * 保存提醒记录 如果提醒的id==-1则执行插入 否则根据id执行更新
	 * 
	 * @param notice
	 * @throws RepaymentNoticeException
	 */
	public void saveRepaymentNotice(RepaymentNotice notice)
			throws RepaymentNoticeException {
		db = helper.getWritableDatabase();
		if (notice.id == -1) {
			insertRepaymentNotice(notice, db);
		} else {
			updateRepaymentNotice(notice, db);
		}
	}

	/**
	 * 验证提示是否存在 如果存在则抛出RepaymentNoticeException
	 * 
	 * @param notice
	 * @throws RepaymentNoticeException
	 */
	public void validate(RepaymentNotice notice)
			throws RepaymentNoticeException {
		Cursor cursor = db.query("REPAYMENT_NOTICE", new String[] { "ID", },
				" NAME = ? AND NOTICE_TYPE = ? ", new String[] {
						notice.name + "", notice.noticeType + "" }, null, null,
				null);
		if (cursor.moveToNext()) {
			if (notice.noticeType == RepaymentNotice.TYPE_CREDIT_CARD) {
				throw new RepaymentNoticeException(String.format(
						"信用卡提醒名称为[%s]已经存在", notice.name));
			} else {
				throw new RepaymentNoticeException(String.format(
						"贷款提醒名称为[%s]已经存在", notice.name));
			}
		}
	}

	/**
	 * 插入提醒记录
	 * 
	 * @param notice
	 * @param db
	 * @throws RepaymentNoticeException
	 */
	private void insertRepaymentNotice(RepaymentNotice notice, SQLiteDatabase db)
			throws RepaymentNoticeException {
		ContentValues values = new ContentValues();
		values.put("NAME", notice.name);
		values.put("NOTICE_TYPE", notice.noticeType);
		values.put("REPAYMENT_DATE", notice.repaymentDate);
		values.put("PRE_NOTICE_DAYS", notice.preNoticeDays);
		values.put("YEAR", notice.year);
		long id = db.insert("REPAYMENT_NOTICE", "ID", values);
		notice.id = id;
	}

	/**
	 * 删除提醒
	 * 
	 * @param info
	 */
	public void deleteRepaymentNotice(RepaymentNoticeInfo info) {
		db = helper.getWritableDatabase();
		// 删除提醒记录
		db.delete("REPAYMENT_NOTICE", " ID = ? ", new String[] { info.id + "" });
		if (info.noticeType == RepaymentNotice.TYPE_CREDIT_CARD) {
			// 删除信用卡记录
			db.delete("CREDIT_CARD_RECORD", " REPAYMENT_NOTICE_ID = ? ", new String[] { info.id
					+ "" });
		} else {
			// 删除贷款记录
			db.delete("CREDIT_PLAN_RECORD", " REPAYMENT_NOTICE_ID = ? ", new String[] { info.id
					+ "" });
		}
	}

	/**
	 * 
	 * 根据id更新提醒信息
	 * 
	 * @param notice
	 * @param db
	 */
	private void updateRepaymentNotice(RepaymentNotice notice, SQLiteDatabase db) {
		ContentValues values = new ContentValues();
		values.put("NAME", notice.name);
		values.put("REPAYMENT_DATE", notice.repaymentDate);
		values.put("PRE_NOTICE_DAYS", notice.preNoticeDays);
		values.put("YEAR", notice.year);
		db.update("REPAYMENT_NOTICE", values, " id = ? ",
				new String[] { notice.id + "" });
	}

	/**
	 * 根据Id获得提醒信息
	 * 
	 * @param noticeId
	 * @return
	 */
	public RepaymentNotice getRepaymentNotice(long noticeId) {
		db = helper.getReadableDatabase();
		Cursor cursor = db.query("REPAYMENT_NOTICE", new String[] { "ID",
				"NAME", "NOTICE_TYPE", "REPAYMENT_DATE", "PRE_NOTICE_DAYS",
				"YEAR" }, " ID = ? ", new String[] { noticeId + "" }, null,
				null, null);
		if (cursor.moveToNext()) {
			RepaymentNotice notice = new RepaymentNotice();
			notice.id = cursor.getLong(0);
			notice.name = cursor.getString(1);
			notice.noticeType = cursor.getInt(2);
			notice.repaymentDate = cursor.getInt(3);
			notice.preNoticeDays = cursor.getInt(4);
			notice.year = cursor.getInt(5);
			return notice;
		}
		return null;
	}

	/**
	 * 获得所有需要提醒的记录
	 * 
	 * @return
	 * @throws RepaymentNoticeException
	 */
	public List<RepaymentNoticeInfo> listAll() throws RepaymentNoticeException {
		db = helper.getReadableDatabase();
		Cursor cursor = db.query("REPAYMENT_NOTICE_INFO", new String[] { "ID",
				"NAME", "NOTICE_TYPE", "PRE_NOTICE_DAYS",
				"PREDICT_REPAYMENT_DATE" }, null, null, null, null, null);
		List<RepaymentNoticeInfo> list = new ArrayList<RepaymentNoticeInfo>();
		while (cursor.moveToNext()) {
			RepaymentNoticeInfo notice = new RepaymentNoticeInfo();
			notice.id = cursor.getLong(0);
			notice.name = cursor.getString(1);
			notice.noticeType = cursor.getInt(2);
			notice.preNoticeDays = cursor.getInt(3);
			notice.predictRepaymentDate = DateUtil.parseDB(cursor.getString(4));
			list.add(notice);
		}
		return list;
	}

	/**
	 * 根据id获得需要提醒的记录
	 * 
	 * @param id
	 * @return
	 * @throws RepaymentNoticeException
	 */
	public RepaymentNoticeInfo getRepaymentNoticeInfo(Long id)
			throws RepaymentNoticeException {
		db = helper.getReadableDatabase();
		Cursor cursor = db.query("REPAYMENT_NOTICE_INFO", new String[] { "ID",
				"NAME", "NOTICE_TYPE", "PRE_NOTICE_DAYS",
				"PREDICT_REPAYMENT_DATE" }, " ID = ? ",
				new String[] { id + "" }, null, null, null);
		if (cursor.moveToNext()) {
			RepaymentNoticeInfo notice = new RepaymentNoticeInfo();
			notice.id = cursor.getLong(0);
			notice.name = cursor.getString(1);
			notice.noticeType = cursor.getInt(2);
			notice.preNoticeDays = cursor.getInt(3);
			notice.predictRepaymentDate = DateUtil.parseDB(cursor.getString(4));
			return notice;
		}
		return null;
	}

	/**
	 * 保存信用卡还款记录 如果oldPredictRepaymentDate是null则添加一条新纪录
	 * 否则根据oldPredictRepaymentDate和record.repaymentNoticeId修改已经存在的记录
	 * 
	 * @param record
	 * @param oldPredictRepaymentDate
	 * @throws RepaymentNoticeException
	 */
	public void saveCreditCardRecord(CreditCardRecord record,
			Date oldPredictRepaymentDate) throws RepaymentNoticeException {
		db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("PREDICT_REPAYMENT_DATE",
				DateUtil.formatDB(record.predictRepaymentDate));
		if (oldPredictRepaymentDate == null) {
			values.put("REPAYMENT_NOTICE_ID", record.repaymentNoticeId);
			values.put("RESULT", record.result);
			db.insert("CREDIT_CARD_RECORD", null, values);
		} else {
			db.update("CREDIT_CARD_RECORD", values,
					"REPAYMENT_NOTICE_ID = ? AND PREDICT_REPAYMENT_DATE = ?",
					new String[] { String.valueOf(record.repaymentNoticeId),
							DateUtil.formatDB(oldPredictRepaymentDate) });
		}
	}

	/**
	 * 
	 * 更新信用卡还款记录
	 * 
	 * @param record
	 * @throws RepaymentNoticeException
	 */
	public void updateCreditCardRecord(CreditCardRecord record)
			throws RepaymentNoticeException {
		db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("REPAYMENT_DATE", DateUtil.formatDB(record.repaymentDate));
		values.put("RESULT", record.result);
		values.put("COMMENT", record.comment);
		db.update("CREDIT_CARD_RECORD", values,
				"REPAYMENT_NOTICE_ID = ? AND PREDICT_REPAYMENT_DATE = ?",
				new String[] { String.valueOf(record.repaymentNoticeId),
						DateUtil.formatDB(record.predictRepaymentDate) });
	}

	/**
	 * 
	 * 根据提醒信息 获得信用卡没有还款的记录
	 * 
	 * @param info
	 * @return
	 * @throws RepaymentNoticeException
	 */
	public CreditCardRecord getCreditCardRecord(RepaymentNoticeInfo info)
			throws RepaymentNoticeException {
		db = helper.getReadableDatabase();
		Cursor cursor = db.query(
				"CREDIT_CARD_RECORD",
				new String[] { "REPAYMENT_NOTICE_ID", "PREDICT_REPAYMENT_DATE",
						"REPAYMENT_DATE", "RESULT", "COMMENT" },
				" REPAYMENT_NOTICE_ID = ? AND PREDICT_REPAYMENT_DATE = ?",
				new String[] { info.id + "",
						DateUtil.formatDB(info.predictRepaymentDate) }, null,
				null, null);
		if (cursor.moveToNext()) {
			CreditCardRecord notice = new CreditCardRecord();
			notice.repaymentNoticeId = cursor.getLong(0);
			notice.predictRepaymentDate = DateUtil.parseDB(cursor.getString(1));
			notice.repaymentDate = DateUtil.parseDB(cursor.getString(2));
			notice.result = cursor.getInt(3);
			notice.comment = cursor.getString(4);
			return notice;
		}
		return null;
	}

	/**
	 * 
	 * 根据提醒信息 获得贷款没有还款的记录
	 * 
	 * @param info
	 * @return
	 * @throws RepaymentNoticeException
	 */
	public CreditPlanRecord getCreditPlanRecord(RepaymentNoticeInfo info)
			throws RepaymentNoticeException {
		db = helper.getReadableDatabase();
		Cursor cursor = db.query(
				"CREDIT_PLAN_RECORD",
				new String[] { "REPAYMENT_NOTICE_ID", "PREDICT_REPAYMENT_DATE","PREDICT_REPAYMENT_AMOUNT",
						"REPAYMENT_DATE","REPAYMENT_AMOUNT", "RESULT", "COMMENT" },
				" REPAYMENT_NOTICE_ID = ? AND PREDICT_REPAYMENT_DATE = ?",
				new String[] { info.id + "",
						DateUtil.formatDB(info.predictRepaymentDate) }, null,
				null, null);
		if (cursor.moveToNext()) {
			CreditPlanRecord record = new CreditPlanRecord();
			record.repaymentNoticeId = cursor.getLong(0);
			record.predictRepaymentDate = DateUtil.parseDB(cursor.getString(1));
			record.predictRepaymentAmount = cursor.getDouble(2);
			record.repaymentDate = DateUtil.parseDB(cursor.getString(3));
			record.repaymentAmount = cursor.getDouble(4);
			record.result = cursor.getInt(5);
			record.comment = cursor.getString(6);
			return record;
		}
		return null;
	}

	/**
	 * 贷款还款
	 * 
	 * @param record
	 * @throws RepaymentNoticeException
	 */
	public void creditPlanRepayment(CreditPlanRecord record)
			throws RepaymentNoticeException {
		db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("REPAYMENT_DATE", DateUtil.formatDB(record.repaymentDate));
		values.put("RESULT", CreditPlanRecord.RESULT_REPAYMENTED);
		values.put("COMMENT", record.comment);
		db.update("CREDIT_PLAN_RECORD", values,
				"REPAYMENT_NOTICE_ID = ? AND PREDICT_REPAYMENT_DATE = ?",
				new String[] { String.valueOf(record.repaymentNoticeId),
						DateUtil.formatDB(record.predictRepaymentDate) });
	}

	/**
	 * 保存贷款还款记录 如果oldPredictRepaymentDate是null则添加一条新纪录
	 * 否则根据oldPredictRepaymentDate和record.repaymentNoticeId修改已经存在的记录
	 * 
	 * @param record
	 * @param oldPredictRepaymentDate
	 * @throws RepaymentNoticeException
	 */
	public void saveCreditPlanRecord(CreditPlanRecord record,
			Date oldPredictRepaymentDate) throws RepaymentNoticeException {
		db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("PREDICT_REPAYMENT_DATE",
				DateUtil.formatDB(record.predictRepaymentDate));
		values.put("PREDICT_REPAYMENT_AMOUNT", record.predictRepaymentAmount);
		values.put("REPAYMENT_AMOUNT", record.repaymentAmount);
		if (oldPredictRepaymentDate == null) {
			values.put("REPAYMENT_NOTICE_ID", record.repaymentNoticeId);
			values.put("RESULT", record.result);

			db.insert("CREDIT_PLAN_RECORD", null, values);
		} else {
			db.update("CREDIT_PLAN_RECORD", values,
					"REPAYMENT_NOTICE_ID = ? AND PREDICT_REPAYMENT_DATE = ?",
					new String[] { String.valueOf(record.repaymentNoticeId),
							DateUtil.formatDB(oldPredictRepaymentDate) });
		}
	}

	/**
	 * 
	 * 更新贷款记录
	 * 
	 * @param record
	 * @throws RepaymentNoticeException
	 */
	public void updateCreditPlanRecord(CreditPlanRecord record)
			throws RepaymentNoticeException {
		db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("REPAYMENT_DATE", DateUtil.formatDB(record.repaymentDate));
		values.put("RESULT", record.result);
		values.put("COMMENT", record.comment);
		db.update("CREDIT_PLAN_RECORD", values,
				"REPAYMENT_NOTICE_ID = ? AND PREDICT_REPAYMENT_DATE = ?",
				new String[] { String.valueOf(record.repaymentNoticeId),
						DateUtil.formatDB(record.predictRepaymentDate) });
	}

}
